package com.corgi.core.modules.sys.service.impl;

import org.hibernate.SQLQuery;
import org.hibernate.transform.AliasToEntityMapResultTransformer;
import org.springframework.stereotype.Service;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import java.util.List;

/**
 * TestRankServiceImpl
 *
 * @title: TestRankServiceImpl
 * @description:
 * @author: dengmiao
 * @create: 2020-05-23 15:27
 **/
@Service
public class TestRankServiceImpl {

    @PersistenceContext
    private EntityManager em;

    // 别名成功注入
    public List getRank() {
        String sql = "SELECT\n" +
                "\tt.id,\n" +
                "\tt.uid,\n" +
                "\tt.score,\n" +
                "CASE\n" +
                "\t\t\n" +
                "\t\tWHEN @rowtotal = t.score THEN\n" +
                "\t\t@rownum \n" +
                "\t\tWHEN @rowtotal \\:= t.score THEN\n" +
                "\t\t@rownum \\:= @rownum + 1 \n" +
                "\t\tWHEN @rowtotal = 0 THEN\n" +
                "\t\t@rownum \\:= @rownum + 1 \n" +
                "\tEND AS rank \n" +
                "FROM\n" +
                "\t( SELECT id, uid, score FROM test_rank ORDER BY score DESC ) AS t,\n" +
                "\t( SELECT @rownum \\:= 0, @rowtotal \\:= NULL ) r";
        List list = em.createNativeQuery(sql)
                .unwrap(SQLQuery.class)
                .setResultTransformer(
                        AliasToEntityMapResultTransformer.INSTANCE
                )
                .list();
        return list;
    }
}
